"""数据库管理模块.

本模块负责数据库管理相关功能:
1. 数据库连接管理
2. SQL查询执行
3. 数据库迁移
4. 事务处理
"""

import os
from pathlib import Path
from typing import List, Optional

from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine
from sqlalchemy.exc import SQLAlchemyError

from ..utils.config import Config
from ..utils.logger import logger


class DatabaseManager:
    """数据库管理类,提供数据库操作和管理功能."""
    
    def __init__(self):
        """初始化数据库管理器."""
        self.engine: Optional[Engine] = None
    
    def get_engine(self) -> Engine:
        """获取或创建数据库引擎.
        
        Returns:
            Engine: SQLAlchemy数据库引擎实例
        """
        if self.engine is None:
            self.engine = create_engine(Config.get_database_url())
        return self.engine
    
    def init_database(self) -> bool:
        """初始化数据库架构.
        
        执行以下步骤:
        1. 获取迁移文件列表
        2. 按顺序执行迁移脚本
        3. 记录迁移状态
        
        Returns:
            bool: 初始化是否成功
        """
        try:
            # 获取迁移文件
            migrations_dir = Path(__file__).parent / "migrations"
            migration_files = sorted([
                f for f in migrations_dir.glob("V*__*.sql")
                if f.is_file()
            ])
            
            if not migration_files:
                logger.warning("未找到迁移文件")
                return False
            
            # 按顺序执行迁移
            with self.get_engine().connect() as conn:
                for migration_file in migration_files:
                    logger.info(f"执行迁移: {migration_file.name}")
                    
                    # 读取并执行迁移脚本
                    with open(migration_file, "r") as f:
                        sql = f.read()
                        conn.execute(text(sql))
                        
                conn.commit()
            
            logger.info("数据库初始化成功完成")
            return True
            
        except Exception as e:
            logger.error(f"数据库初始化时发生错误: {e}")
            return False
    
    def execute_query(self, query: str, params: Optional[dict] = None) -> List:
        """执行SQL查询.
        
        Args:
            query: SQL查询语句
            params: 可选的查询参数
            
        Returns:
            List: 查询结果列表
        
        Raises:
            SQLAlchemyError: 数据库操作错误
        """
        try:
            with self.get_engine().connect() as conn:
                result = conn.execute(
                    text(query),
                    params or {}
                )
                return [dict(row) for row in result]
                
        except SQLAlchemyError as e:
            logger.error(f"数据库查询错误: {e}")
            raise
    
    def execute_transaction(
        self,
        queries: List[tuple]
    ) -> bool:
        """在事务中执行多个查询.
        
        Args:
            queries: 查询和参数元组的列表
            
        Returns:
            bool: 事务是否成功
        """
        try:
            with self.get_engine().begin() as conn:
                for query, params in queries:
                    conn.execute(text(query), params or {})
            return True
            
        except SQLAlchemyError as e:
            logger.error(f"事务执行错误: {e}")
            return False
    
    def check_connection(self) -> bool:
        """检查数据库连接.
        
        Returns:
            bool: 连接是否成功
        """
        try:
            with self.get_engine().connect() as conn:
                conn.execute(text("SELECT 1"))
            return True
            
        except SQLAlchemyError as e:
            logger.error(f"数据库连接错误: {e}")
            return False
    
    def get_table_names(self) -> List[str]:
        """获取数据库中所有表名.
        
        Returns:
            List[str]: 表名列表
        """
        try:
            with self.get_engine().connect() as conn:
                result = conn.execute(text("""
                    SELECT table_name 
                    FROM information_schema.tables 
                    WHERE table_schema = 'public'
                """))
                return [row[0] for row in result]
                
        except SQLAlchemyError as e:
            logger.error(f"获取表名列表时发生错误: {e}")
            return []